/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package DAL;

import BE.Artist;
import BE.Category;
import BE.Song;
import com.microsoft.sqlserver.jdbc.SQLServerException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author BentH
 */
public class SongDBManager
{

    private static SongDBManager instance = null;
    private final DBConnectionManager conMgr;

    private SongDBManager() throws IOException
    {
        conMgr = DBConnectionManager.getInstance();
    }

    public static SongDBManager getInstance() throws IOException
    {
        if (instance == null)
        {
            instance = new SongDBManager();
        }
        return instance;
    }

    public Song insert(Song s) throws SQLServerException, SQLException
    {
        try (Connection con = conMgr.getConnection())
        {
            String sql = "INSERT INTO Song VALUES(?, ?, ?, ?, ?, ?)";
            PreparedStatement ps = con.prepareStatement(sql,
                    PreparedStatement.RETURN_GENERATED_KEYS);
            ps.setString(1, s.getTitle());
            ps.setInt(2, s.getArtist().getId());
            ps.setInt(3, s.getCategory().getId());
            ps.setInt(4, s.getBMP());
            ps.setInt(5, s.getDuration());
            ps.setBoolean(6, s.isFavorite());
            int affectedRows = ps.executeUpdate();
            if (affectedRows == 0)
            {
                throw new SQLException("Unable to add song");
            }
            ResultSet keySet = ps.getGeneratedKeys();
            keySet.next();
            int songId = keySet.getInt(1);
            return new Song(songId, s);
        }
    }

    List<Song> getAll() throws SQLException, IOException
    {
        List<Song> songs = new ArrayList<>();
        try (Connection con = conMgr.getConnection())
        {
            String sql = "SELECT * FROM Song";
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery(sql);
            while (rs.next())
            {
                songs.add(getOneSong(rs));
            }
            return songs;
        }
    }

    public Song getById(int id) throws SQLException, IOException
    {
        try (Connection con = conMgr.getConnection())
        {
            String sql = "SELECT * FROM Song WHERE id = ?";
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setInt(1, id);
            ResultSet rs = ps.executeQuery();
            if (rs.next())
            {
                return getOneSong(rs);
            }
        }
        return null;
    }

    List<Song> getSongsByArtistId(int id) throws SQLException, IOException
    {
        List<Song> songs = new ArrayList<>();
        try (Connection con = conMgr.getConnection())
        {
            String sql = "SELECT * FROM Song WHERE ArtistId = ?";
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setInt(1, id);
            ResultSet rs = ps.executeQuery();
            while (rs.next())
            {
                songs.add(getOneSong(rs));
            }
        }
        return songs;
    }

    private Song getOneSong(ResultSet rs) throws IOException, SQLException
    {
        int id = rs.getInt("Id");
        String title = rs.getString("Title");
        Artist artist = ArtistDBManager.getInstance().getById(rs.getInt("ArtistId"));
        Category category = CategoryDBManager.getInstance().getById(rs.getInt("CategoryId"));
        int BMP = rs.getInt("BMP");
        int duration = rs.getInt("Duration");
        boolean favorite = rs.getBoolean("favorite");
        return new Song(id, title, artist, category, BMP, duration, favorite);
    }

    List<Song> getSongsByCategoryId(int id) throws SQLException, IOException
    {
        List<Song> songs = new ArrayList<>();
        try (Connection con = conMgr.getConnection())
        {
            String sql = "SELECT * FROM Song WHERE CategoryId = ?";
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setInt(1, id);
            ResultSet rs = ps.executeQuery();
            while (rs.next())
            {
                songs.add(getOneSong(rs));
            }
        }
        return songs;
    }
}
